package com.zhangtai.modules.dao;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.zhangtai.modules.dto.EffectTimeDto;
import com.zhangtai.modules.entity.employee.EmployeeEducationEntity;
import com.zhangtai.modules.entity.employee.EmployeeInfoEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.Date;
import java.util.List;

/**
 * 
 * 
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2020-06-15 11:54:06
 */
@Mapper
public interface EmployeeInfoDao extends BaseMapper<EmployeeInfoEntity> {

    //根据时段, 被操作人公司组 统计招聘人员

    @Select("Select count(*) from tb_employee_info where DATEDIFF(hiredate,#{date})>365 and DATE_FORMAT(hiredate,'%m') = #{month}")
     int getPreAnniversaryCount(@Param("date")String date, @Param("month")String month);

    @Select("Select count(*) from tb_employee_info where  DATE_FORMAT(birthday,'%m') = #{month}")
	int getBirthDayCount(@Param("month")String month);


    @Select("<script>select name,case gender when '1' then '男' when '2' then '女' end,idcard,code,nation,nationality," +
            "nativeplace,mobile,polity,regresidence,address,privatemail," +
            "case healthstatus when '1' then '非常好' when'2' then '良好' when'3' then '一般' when'4' then '有生理缺陷' when'5' then '残废' end healthstatus," +
            "case disbandment when '1' then '是' when '2' then '否' end disbandment,case marital when '1' then '已婚' when '2' then '未婚' end marital," +
            "beginworkdate,birthday,contract,unitname,orgname,jobname,job_level,hiredate,probationday,regular_day,channel,recruitment_web,inner_referrer," +
            "outer_referrer,work_address,case is_reinstate when '1' then '是' else '否' end is_reinstate from tb_employee_info" +
                "<where>" +
                    "<if test='keyword!=null'>name=#{keyword}</if>" +
                    "<if test='isReinstate!=null'>is_reinstate=#{isReinstate}</if>" +
                    "<if test='status!=null and status.size>0'>  is_reinstate in " +
                        " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
                    "</if>"+
                "</where>" +
            "</script>")
    List getExportEmpolyeeInfo( @Param(value = "keyword") String keyword,
                                @Param(value = "status") List<Integer> status,
                                @Param(value="isReinstate") Integer isReinstate);
    
    @Select("SELECT count(employee_oid) FROM tb_employee_info WHERE inset_date = #{insetDate}")
    Long getEmployeeOidList(@Param("insetDate")String insetDate);


    @Select("Select * from tb_employee_info where is_delete = 0 " +
            "and (is_regular = 0 or is_regular = 2) " +
            "and employeestatus = 2 " +
            "and job_type NOT IN (1,2,3,5) "+
            "and regular_is_done = 0 " +
            "and is_entry_status = 1 " +
            "and is_regular_remind = 0 " +
            "and is_reinstate != 2 " +
            "and TIMESTAMPDIFF(DAY,#{time},regular_day) <=20")
   List<EmployeeInfoEntity> getTodoRegularList(@Param(value = "time") String time);

    //根据日查询 招聘人数和操作人EffectTimeDto
    @Select("<script>SELECT DATE_FORMAT(create_time,'%Y%m%d')  TimeQue,principal creator,count(NAME) num FROM tb_employee_info" +
            "<where> is_delete=0  AND is_reinstate IN (0,1,3) " +
            "and create_time &lt;= #{endTime} " +
            "AND create_time &gt;= #{beginTime} " +
            "<if test='orgIds!=null and orgIds.size>0'> and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+

//            "<if test='orgIdsByGroupId==null or orgIdsByGroupId.size==0'> and (org_id = null ) </if>" +
            " GROUP BY principal,TimeQue "+
            "</where>" +
            "</script>")
    List<EffectTimeDto> getDayRecruitList(@Param("orgIds") List<Long> orgIds,
                                          @Param("beginTime") String beginTime,
                                          @Param("endTime") String endTime);

}
